<!DOCTYPE html>
<html>
<head>
<title>sql.md</title>
<meta http-equiv="Content-type" content="text/html;charset=UTF-8">

<style>
/*---------------------------------------------------------------------------------------------
 *  Copyright (c) Microsoft Corporation. All rights reserved.
 *  Licensed under the MIT License. See License.txt in the project root for license information.
 *--------------------------------------------------------------------------------------------*/

body {
	font-family: "Segoe WPC", "Segoe UI", "SFUIText-Light", "HelveticaNeue-Light", sans-serif, "Droid Sans Fallback";
	font-size: 14px;
	padding: 0 12px;
	line-height: 22px;
	word-wrap: break-word;
}

#code-csp-warning {
	position: fixed;
	top: 0;
	right: 0;
	color: white;
	margin: 16px;
	text-align: center;
	font-size: 12px;
	font-family: sans-serif;
	background-color:#444444;
	cursor: pointer;
	padding: 6px;
	box-shadow: 1px 1px 1px rgba(0,0,0,.25);
}

#code-csp-warning:hover {
	text-decoration: none;
	background-color:#007acc;
	box-shadow: 2px 2px 2px rgba(0,0,0,.25);
}


body.scrollBeyondLastLine {
	margin-bottom: calc(100vh - 22px);
}

body.showEditorSelection .code-line {
	position: relative;
}

body.showEditorSelection .code-active-line:before,
body.showEditorSelection .code-line:hover:before {
	content: "";
	display: block;
	position: absolute;
	top: 0;
	left: -12px;
	height: 100%;
}

body.showEditorSelection li.code-active-line:before,
body.showEditorSelection li.code-line:hover:before {
	left: -30px;
}

.vscode-light.showEditorSelection .code-active-line:before {
	border-left: 3px solid rgba(0, 0, 0, 0.15);
}

.vscode-light.showEditorSelection .code-line:hover:before {
	border-left: 3px solid rgba(0, 0, 0, 0.40);
}

.vscode-dark.showEditorSelection .code-active-line:before {
	border-left: 3px solid rgba(255, 255, 255, 0.4);
}

.vscode-dark.showEditorSelection .code-line:hover:before {
	border-left: 3px solid rgba(255, 255, 255, 0.60);
}

.vscode-high-contrast.showEditorSelection .code-active-line:before {
	border-left: 3px solid rgba(255, 160, 0, 0.7);
}

.vscode-high-contrast.showEditorSelection .code-line:hover:before {
	border-left: 3px solid rgba(255, 160, 0, 1);
}

img {
	max-width: 100%;
	max-height: 100%;
}

a {
	color: #4080D0;
	text-decoration: none;
}

a:focus,
input:focus,
select:focus,
textarea:focus {
	outline: 1px solid -webkit-focus-ring-color;
	outline-offset: -1px;
}

hr {
	border: 0;
	height: 2px;
	border-bottom: 2px solid;
}

h1 {
	padding-bottom: 0.3em;
	line-height: 1.2;
	border-bottom-width: 1px;
	border-bottom-style: solid;
}

h1, h2, h3 {
	font-weight: normal;
}

h1 code,
h2 code,
h3 code,
h4 code,
h5 code,
h6 code {
	font-size: inherit;
	line-height: auto;
}

a:hover {
	color: #4080D0;
	text-decoration: underline;
}

table {
	border-collapse: collapse;
}

table > thead > tr > th {
	text-align: left;
	border-bottom: 1px solid;
}

table > thead > tr > th,
table > thead > tr > td,
table > tbody > tr > th,
table > tbody > tr > td {
	padding: 5px 10px;
}

table > tbody > tr + tr > td {
	border-top: 1px solid;
}

blockquote {
	margin: 0 7px 0 5px;
	padding: 0 16px 0 10px;
	border-left: 5px solid;
}

code {
	font-family: Menlo, Monaco, Consolas, "Droid Sans Mono", "Courier New", monospace, "Droid Sans Fallback";
	font-size: 14px;
	line-height: 19px;
}

body.wordWrap pre {
	white-space: pre-wrap;
}

.mac code {
	font-size: 12px;
	line-height: 18px;
}

pre:not(.hljs),
pre.hljs code > div {
	padding: 16px;
	border-radius: 3px;
	overflow: auto;
}

/** Theming */

.vscode-light,
.vscode-light pre code {
	color: rgb(30, 30, 30);
}

.vscode-dark,
.vscode-dark pre code {
	color: #DDD;
}

.vscode-high-contrast,
.vscode-high-contrast pre code {
	color: white;
}

.vscode-light code {
	color: #A31515;
}

.vscode-dark code {
	color: #D7BA7D;
}

.vscode-light pre:not(.hljs),
.vscode-light code > div {
	background-color: rgba(220, 220, 220, 0.4);
}

.vscode-dark pre:not(.hljs),
.vscode-dark code > div {
	background-color: rgba(10, 10, 10, 0.4);
}

.vscode-high-contrast pre:not(.hljs),
.vscode-high-contrast code > div {
	background-color: rgb(0, 0, 0);
}

.vscode-high-contrast h1 {
	border-color: rgb(0, 0, 0);
}

.vscode-light table > thead > tr > th {
	border-color: rgba(0, 0, 0, 0.69);
}

.vscode-dark table > thead > tr > th {
	border-color: rgba(255, 255, 255, 0.69);
}

.vscode-light h1,
.vscode-light hr,
.vscode-light table > tbody > tr + tr > td {
	border-color: rgba(0, 0, 0, 0.18);
}

.vscode-dark h1,
.vscode-dark hr,
.vscode-dark table > tbody > tr + tr > td {
	border-color: rgba(255, 255, 255, 0.18);
}

.vscode-light blockquote,
.vscode-dark blockquote {
	background: rgba(127, 127, 127, 0.1);
	border-color: rgba(0, 122, 204, 0.5);
}

.vscode-high-contrast blockquote {
	background: transparent;
	border-color: #fff;
}
</style>

<style>
/* Tomorrow Theme */
/* http://jmblog.github.com/color-themes-for-google-code-highlightjs */
/* Original theme - https://github.com/chriskempson/tomorrow-theme */

/* Tomorrow Comment */
.hljs-comment,
.hljs-quote {
	color: #8e908c;
}

/* Tomorrow Red */
.hljs-variable,
.hljs-template-variable,
.hljs-tag,
.hljs-name,
.hljs-selector-id,
.hljs-selector-class,
.hljs-regexp,
.hljs-deletion {
	color: #c82829;
}

/* Tomorrow Orange */
.hljs-number,
.hljs-built_in,
.hljs-builtin-name,
.hljs-literal,
.hljs-type,
.hljs-params,
.hljs-meta,
.hljs-link {
	color: #f5871f;
}

/* Tomorrow Yellow */
.hljs-attribute {
	color: #eab700;
}

/* Tomorrow Green */
.hljs-string,
.hljs-symbol,
.hljs-bullet,
.hljs-addition {
	color: #718c00;
}

/* Tomorrow Blue */
.hljs-title,
.hljs-section {
	color: #4271ae;
}

/* Tomorrow Purple */
.hljs-keyword,
.hljs-selector-tag {
	color: #8959a8;
}

.hljs {
	display: block;
	overflow-x: auto;
	color: #4d4d4c;
	padding: 0.5em;
}

.hljs-emphasis {
	font-style: italic;
}

.hljs-strong {
	font-weight: bold;
}
</style>

<style>
/*
 * Markdown PDF CSS
 */

 body {
	font-family:  "Meiryo", "Segoe WPC", "Segoe UI", "SFUIText-Light", "HelveticaNeue-Light", sans-serif, "Droid Sans Fallback";
}

pre {
	background-color: #f8f8f8;
	border: 1px solid #cccccc;
	border-radius: 3px;
	overflow-x: auto;
	white-space: pre-wrap;
	overflow-wrap: break-word;
}

pre:not(.hljs) {
	padding: 23px;
	line-height: 19px;
}

blockquote {
	background: rgba(127, 127, 127, 0.1);
	border-color: rgba(0, 122, 204, 0.5);
}

.emoji {
	height: 1.4em;
}

/* for inline code */
:not(pre):not(.hljs) > code {
	color: #C9AE75; /* Change the old color so it seems less like an error */
	font-size: inherit;
}

/* Page Break : use <div class="page"/> to insert page break
-------------------------------------------------------- */
.page {
	page-break-after: always;
}

</style>

</head>
<body>
<hr>
<h2 id="layout-posttitle-t-sql-cheatsheetcategory-computersciencetags-t-sql">layout: post
title: T-SQL Cheatsheet
category: ComputerScience<br>
tags: [T-sql]</h2>
<h2 id="basics">Basics</h2>
<table>
  <tr>
    <td>Create Table</td>
    <td>create table table1( id varchar(300) primary key, name varchar(200) not null);</td>
  </tr>
  <tr>
    <td>Insert </td>
    <td>insert into table1 (id,name) values ('aa','bb');</td>
  </tr>
  <tr>
    <td>Delete</td>
    <td>delete from table1 where id ='cc';</td>
  </tr>
  <tr>
    <td>Update</td>
    <td>update table1 set id = 'bb' where id='cc';</td>
  </tr>
  <tr>
    <td>Query</td>
    <td>select id,name (case gender when 0 then 'male' when 1 then ‘femal’ end  ) gender from  table1</td>
  </tr>
  <tr>
    <td>Delete Table  </td>
    <td>drop table table1;</td>
  </tr>
  <tr>
    <td>Change Table Title </td>
    <td>alter table table1 rename to table2;</td>
  </tr>
  <tr>
    <td>Copy Table Content</td>
    <td>insert into table1 (select * from table2);</td>
  </tr>
  <tr>
    <td>Copy Table Structure</td>
    <td>create table table1 select * from table2 where 1&gt;1;</td>
  </tr>
  <tr>
    <td>Copy Table Content andStructure</td>
    <td>create table table1 select * from table2;</td>
  </tr>
  <tr>
    <td>Copy Selected Field</td>
    <td>create table table1 as select id, name from table2 where 1&gt;1;</td>
  </tr>
</table>
<h2 id="math-functions">Math Functions</h2>
<table>
  <tr>
    <td>Absolute value: abs()</td>
    <td>select abs(-2) value from dual;</td>
    <td>2</td>
  </tr>
  <tr>
    <td>Ceiling: ceil()</td>
    <td>select ceil(-2.001) value from dual;</td>
    <td>-2</td>
  </tr>
  <tr>
    <td>Florr: floor()</td>
    <td>select floor(-2.001) value from dual;</td>
    <td>-3</td>
  </tr>
  <tr>
    <td>Trunc: trunc()</td>
    <td>select trunc(-2.001) value from dual;</td>
    <td>-2</td>
  </tr>
  <tr>
    <td>Round-off: round()</td>
    <td>select round(1.234564,4) value from dual;</td>
    <td>1.2346</td>
  </tr>
  <tr>
    <td>N-th Power: power(m,n)</td>
    <td>select power(4,2) value from dual;</td>
    <td>16</td>
  </tr>
  <tr>
    <td>Square Root: SQRT()</td>
    <td>select sqrt(16) value from dual;</td>
    <td>4</td>
  </tr>
  <tr>
    <td>Random Number: dbms_random(minvalue,maxvalue)</td>
    <td>select dbms_random.value() from dual;<br>select dbms_random.value(2,4) value from dual;</td>
    <td>  </td>
  </tr>
  <tr>
    <td>Sign: Sign()</td>
    <td>select sign(-3) value from dual;</td>
    <td>-1</td>
  </tr>
  <tr>
    <td>Greatest Value: greatest(value)</td>
    <td>select greatest(-1,3,5,7,9) value from dual;</td>
    <td>9</td>
  </tr>
  <tr>
    <td>LeastValue: least(value)</td>
    <td>select least(-1,3,5,7,9) value from dual;</td>
    <td>-1</td>
  </tr>
  <tr>
    <td>Deal with NULL</td>
    <td>select  nvl(null,10) value from dual;</td>
    <td>10</td>
  </tr>
</table>
<h2 id="rownum">Rownum</h2>
<table>
  <tr>
    <td>Selectfrom top n rows.(Oracle Don’t Support Selecttop)</td>
    <td>select * from student where rownum &lt;3;</td>
  </tr>
  <tr>
    <td>Select from table but top n rows.</td>
    <td>select * from(select rownum rn ,id,name from student) where rn&gt;2;<br>select * from (select rownum rn, student.* from student) where rn &gt;3;</td>
  </tr>
  <tr>
    <td>Select from a region</td>
    <td>select * from (select rownum rn, student.* from student) where rn &gt;3 and rn&lt;6;</td>
  </tr>
  <tr>
    <td>Sort and select from top.</td>
    <td>select * from (select rownum rn, t.* from ( select d.* from DJDRUVER d order  bydrivernumber)t )p where p.rn&lt;10;</td>
  </tr>
  <tr>
    <td>Sort and select fromregion.</td>
    <td>select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn&lt;9 and p.rn&gt;6;</td>
  </tr>
  <tr>
    <td>Sort and select from region,another way.</td>
    <td>select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t where rownum&lt;9 )p where p.rn&gt;6;</td>
  </tr>
</table>
<h2 id="paging-query-10-terms-a-page">Paging Query (10 terms a page)</h2>
<h3 id="without-sorting">Without sorting</h3>
<table>
  <tr>
    <td>low efficiency</td>
    <td>select * from (select rownum rn, d.* from DJDRIVER d  )p where p.rn&lt;=20 and p.rn&gt;=10;<br>select * from (select rownum rn, d.* from DJDRIVER d  )p where p.rn between 10 and 20;</td>
  </tr>
  <tr>
    <td>high efficiency</td>
    <td>select * from (select rownum rn, d.* from DJDRIVER d where rownum&lt;=20 )p where p.rn&gt;=10;</td>
  </tr>
</table>
<h3 id="with-sorting">With Sorting</h3>
<table>
  <tr>
    <td>Sort and query a region</td>
    <td>select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn&lt;=20 and p.rn&gt;=10;</td>
  </tr>
  <tr>
    <td>(low efficiency)</td>
    <td>select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn between 10 and 20;</td>
  </tr>
  <tr>
    <td>Sort and query a region(high efficiency) </td>
    <td>select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t where rownum&lt;=20 )p where p.rn&gt;=10;</td>
  </tr>
</table>
 
## String Manipulation
<table>
  <tr>
    <td>Substring(start from 1)</td>
    <td>substr('abcdefg',1,5)</td>
    <td>Abcde</td>
  </tr>
  <tr>
    <td>Search substring</td>
    <td>instr('abcdefg','bc')</td>
    <td>TRUE</td>
  </tr>
  <tr>
    <td>Append strings</td>
    <td>'Hello'||'World' </td>
    <td>HelloWorld</td>
  </tr>
  <tr>
    <td>Deletewhitespace</td>
    <td>trim('  Wish ') </td>
    <td>Wish</td>
  </tr>
  <tr>
    <td>Deletewhitespace before the string</td>
    <td>rtrim('Wish  ') </td>
    <td>Wish</td>
  </tr>
  <tr>
    <td>Deletewhitespace after the string</td>
    <td>ltrim('  Wish') </td>
    <td>wish</td>
  </tr>
  <tr>
    <td>Delete prefix</td>
    <td>trim(leading 'w' from 'wish')</td>
    <td>ish</td>
  </tr>
  <tr>
    <td>Delete trailing</td>
    <td>trim(trailing 'h' from 'wish') </td>
    <td>wis</td>
  </tr>
  <tr>
    <td>Delete </td>
    <td>trim('w' from 'wish')</td>
    <td>ish</td>
  </tr>
  <tr>
    <td rowspan="2">Ascii convert</td>
    <td>ascii('A') </td>
    <td>65</td>
  </tr>
  <tr>
    <td>ascii('a')</td>
    <td>97</td>
  </tr>
  <tr>
    <td rowspan="2">Character convert</td>
    <td>chr(65)</td>
    <td>A</td>
  </tr>
  <tr>
    <td>chr(97)</td>
    <td>a</td>
  </tr>
  <tr>
    <td>length </td>
    <td>length('abcdefg')   </td>
    <td>7</td>
  </tr>
  <tr>
    <td rowspan="3">Capitalize</td>
    <td>lower('WISH') </td>
    <td>wish</td>
  </tr>
  <tr>
    <td>upper('wish') </td>
    <td>WISH</td>
  </tr>
  <tr>
    <td>initcap('wish')</td>
    <td>Wish</td>
  </tr>
  <tr>
    <td>Replace</td>
    <td>replace('wish1','1','youhappy') </td>
    <td>wishyouhappy</td>
  </tr>
  <tr>
    <td rowspan="2">Translate(string,from_str,to_str).<br>Replace every character in string that appeared in from_str to appropriateone in to_str.</td>
    <td>translate('wish1','1','y') </td>
    <td>wishy</td>
  </tr>
  <tr>
    <td>translate('wish1','sh1','hy')</td>
    <td>wihy</td>
  </tr>
  <tr>
    <td>Connect</td>
    <td>concat('11','22')</td>
    <td>1122</td>
  </tr>
</table>
<h2 id="aggregate-function">Aggregate Function</h2>
<table>
  <tr>
    <td>Term number</td>
    <td>count (distinct|all)</td>
  </tr>
  <tr>
    <td>Average</td>
    <td>avg (distinct|all)</td>
  </tr>
  <tr>
    <td>MaximumValue</td>
    <td>max (distinct|all)</td>
  </tr>
  <tr>
    <td>MinimumValue</td>
    <td>min (distinct|all)</td>
  </tr>
  <tr>
    <td>Standard Deviation</td>
    <td>stddev(distinct|all)</td>
  </tr>
  <tr>
    <td>Sum</td>
    <td>sum(distinct|all)</td>
  </tr>
  <tr>
    <td>Median</td>
    <td>median(distinct|all)</td>
  </tr>

</body>
</html>
